import mysql.connector
from mysql.connector import Error

class MySQLDB:
    def __init__(self, host, user, password, database):
        self.host = host
        self.user = user
        self.password = password
        self.database = database
        self.connection = None
        self.cursor = None

    def connect(self):
        try:
            self.connection = mysql.connector.connect(
                host=self.host,
                user=self.user,
                password=self.password,
                database=self.database
            )
            self.cursor = self.connection.cursor()
            print("Connected to MySQL Database successfully.")
        except Error as e:
            print(f"Error while connecting to MySQL: {e}")

    def execute_query(self, query, params=()):
        try:
            # print(query, params)
            self.cursor.execute(query, params)
            self.connection.commit()
            print("Query successful.")
        except Error as e:
            print(f"Error while executing query: {e}")

    def insert(self, table, columns, values):
        query = f"INSERT INTO {table} ({', '.join(columns)}) VALUES ({', '.join(['%s'] * len(values))})"
        self.execute_query(query, values)

    def update(self, table, updates, condition):
        query = f"UPDATE {table} SET {', '.join([f'{k}=%s' for k in updates.keys()])} WHERE {condition}"
        values = list(updates.values())  # Append None as a placeholder for the condition
        self.execute_query(query, values)

    def delete(self, table, condition):
        query = f"DELETE FROM {table} WHERE {condition}"
        self.execute_query(query)

    def select_all(self,table):
        query = f"SELECT * FROM {table}"
        self.cursor.execute(query)
        return self.cursor.fetchall()

    def close(self):
        if self.cursor:
            self.cursor.close()
        if self.connection:
            self.connection.close()
        print("MySQL connection closed.")

# 使用示例
if __name__ == "__main__":
    # 创建MySQLDB对象，并传入数据库配置信息
    db = MySQLDB(host='10.200.32.179', user='root', password='111111', database='b23015135')
    
    # 连接到数据库
    db.connect()
    
    # 查询数据示例
    # print(db.select_all('images_qa'))


    # 插入数据示例
    # db.insert('images_qa', ['image_path', 'question','answer'], ['./test.jpg', '你好', '你好'])
    
    # 更新数据示例（假设你要根据某个条件更新column1的值）
    # db.update('images_qa', {'image_path': 'test.jpg'}, 'id = 2')
    
    # 删除数据示例（假设你要根据某个条件删除记录）
    db.delete('images_qa', 'id = 2')
    
    # 关闭数据库连接
    db.close()